<?php
if (!defined('BASEPATH')) exit('No direct script access allowed');

class Favorites_mod extends CI_Model
{   
    
    private $_dbTableFavorites = 'favorites';
    private $_dbTableUsers = 'users';
    private $_dbTableImages = 'images';
    /**
     *  class sonstructor
     */
    function __construct() 
	{
		// Call the Model constructor
        parent::__construct();
	}
    
    public function getMyFavoritesList($id,$name=NULL)
    {
        // SELECT  favorites.id as favorite_id, ( SELECT name FROM images WHERE `private`=0 and `user_id`= favorite_id ORDER BY main desc LIMIT 0,1 ) as image_name, users.username, users.flirt_text  FROM favorites LEFT JOIN users ON favorites.favorite_id = users.id ORDER BY favorite_id
        $sql = "SELECT {$this->_dbTableFavorites}.id,
                       {$this->_dbTableFavorites}.user_id,
                       {$this->_dbTableFavorites}.favorite_id,
                       {$this->_dbTableUsers}.username,
                       {$this->_dbTableUsers}.name,
                       {$this->_dbTableUsers}.flirt_text,
                       ( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and `user_id`= favorite_id ORDER BY main desc LIMIT 0,1 ) as image_name
                FROM  {$this->_dbTableFavorites}, {$this->_dbTableUsers}
                WHERE {$this->_dbTableFavorites}.favorite_id = {$this->_dbTableUsers}.id
                      AND {$this->_dbTableFavorites}.user_id = $id ";
        if( $name != "" )
          $sql .= " AND ( {$this->_dbTableUsers}.name LIKE '%{$name}%' OR {$this->_dbTableUsers}.username LIKE '%{$name}%' ) ";
          
        $sql .= "     ORDER BY {$this->_dbTableFavorites}.timestamp DESC;
               ";
        $query = $this->db->query($sql);
        
        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
          throw new Exception("SQL Error: " . $errors);
        } else { }

        if ($query->num_rows() > 0) {
          $result = $query->result();
          $query->free_result();
        } else {
          $result = NULL;
        }

        return $result;
    }
    
    /**
     * for Library Favorites
     * @return stdClass array list favorites
     * @throws Exception
     */
    public function getMyFavorites()
    {
        $id = $this->session->userdata('user_id');
        // SELECT  favorites.id as favorite_id, ( SELECT name FROM images WHERE `private`=0 and `user_id`= favorite_id ORDER BY main desc LIMIT 0,1 ) as image_name, users.username, users.flirt_text  FROM favorites LEFT JOIN users ON favorites.favorite_id = users.id ORDER BY favorite_id
        $sql = "SELECT {$this->_dbTableFavorites}.id,
                       {$this->_dbTableFavorites}.user_id,
                       {$this->_dbTableFavorites}.favorite_id,
                       {$this->_dbTableUsers}.username,
                       {$this->_dbTableUsers}.name,
                       ( SELECT name  FROM {$this->_dbTableImages} WHERE `private`=0 and `user_id`= favorite_id ORDER BY main desc LIMIT 0,1 ) as image_name
                FROM  {$this->_dbTableFavorites}, {$this->_dbTableUsers}
                WHERE {$this->_dbTableFavorites}.favorite_id = {$this->_dbTableUsers}.id
                      AND {$this->_dbTableFavorites}.user_id = $id
                ORDER BY {$this->_dbTableFavorites}.timestamp DESC;
               ";
        $query = $this->db->query($sql);
        
        //echo $this->db->last_query();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
          throw new Exception("SQL Error: " . $errors);
        } else { }

        if ($query->num_rows() > 0) {
          $result = $query->result();
          $query->free_result();
        } else {
          $result = NULL;
        }

        return $result;
    }
    
    public function isMyFavorite($favorite_id)
    {
        $id = $this->session->userdata('user_id');
        $query = $this->db->select('id')
                          ->from($this->_dbTableFavorites)
                          ->where('user_id',$id)
                          ->where('favorite_id',$favorite_id)
                          ->get();
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
          throw new Exception("SQL Error: " . $errors);
        } else { }

        if ($query->num_rows() > 0) {
          $result = true;
          $query->free_result();
        } else {
          $result = false;
        }

        return $result;
    }
    
    
    /**
     *  AJAX
     */
    
    public function add_favorite($favorite_id)
    {
        $data = array(
                    'user_id'=>$this->session->userdata('user_id'),
                    'favorite_id' => $favorite_id
                );
        $this->db->insert($this->_dbTableFavorites,$data);
        
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
          throw new Exception("SQL Error: " . $errors);
          return false;
        } else { }
        
        if( $this->db->affected_rows() == 1 ) {
          $result = true;          
        }
        else {
          $result = false;
        }
        
        return $result;
    }

    public function remove_favorite($id)
    {        
        $this->db->where('favorite_id',$id);
        $this->db->where('user_id',$this->session->userdata('user_id'));
        $this->db->delete($this->_dbTableFavorites);
        
        $errors = $this->db->_error_message();
        if (!empty($errors)) {
          throw new Exception("SQL Error: " . $errors);
          return false;
        } else { }
        
        return true;
    }
    
    
}// end class Favorites_mod

/*
      $query = $this->db->select('*')
                        ->from()
                        ->where()
                        ->get();
      //echo $this->db->last_query();
      $errors = $this->db->_error_message();
      if (!empty($errors)) {
        throw new Exception("SQL Error: " . $errors);
      } else { }

      if ($query->num_rows() > 0) {
        $result = $query->result();
        $query->free_result();
      } else {
        $result = NULL;
      }

      return $result;
 */

// end file: application/models/favorites_mod.php

?>